package unipi.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class SQLMethods
{	
	private static String connString = "jdbc:mysql://localhost:3306/jbjbdatabase";
	private static String username = "jbuser";
	private static String password = "resubj";
	
	
	private static Connection InitializeConnection()
	{
		Connection conn = null;		
		
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(connString, username, password);	
		}
		catch(Exception e)
		{
			System.out.println("mySQL Connection Exception : " + e.toString());
			e.printStackTrace();
			return null;			
		}		
		
		return conn;
	}
	
	public static List<List<String>> Select(String query, int ncols)
	{
		List<List<String>> L = new ArrayList<List<String>>();
		
		try
		{			
			Connection conn = InitializeConnection();			
			PreparedStatement statement = conn.prepareStatement(query);		
			ResultSet result = statement.executeQuery();
			
			while(result.next())
			{
				List<String> row = new ArrayList<String>();
				
				for(int x = 1; x <= ncols; x++)
				{					
					row.add(result.getString(x));
				}
				
				L.add(row);
			}		
			
			statement.close();
			result.close();
			conn.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("This query '" + query + "' raised this SQLException " + sqle.toString());
			return null;
		}
		catch(Exception e)
		{
			System.out.println("Exception " + e.toString());
			
			return null;			
		}
		
		return L;
		
	}
	
	public static List<List<String>> Select(String cols, String from, int ncols)
	{
		return Select("select " + cols + " from " + from, ncols);
	}
	
	private static int Execute(String query)
	{
		int id = -2; //because cases where no keys are generated are not errors.
		
		try
		{
			Connection conn = InitializeConnection();
			PreparedStatement statement = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			
			statement.executeUpdate();		
			
			
			ResultSet rs = statement.getGeneratedKeys();
			if(rs.next())
			{
				id = rs.getInt(1);				
			}
			
			statement.close();
			conn.close();
		}
		catch(SQLException sqle)
		{
			System.out.println("This query '" + query + "' raised this SQLException " + sqle.toString());
			return -1;
		}
		catch(Exception e)
		{
			System.out.println("Exception " + e.toString());
			
			return -1;			
		}
		
		return id;		
	}
	
	public static int Insert(String table, String columns, String values) 
	{		
		return Execute("insert into " + table + " (" + columns + ") values (" + values + ")");		
	}
	
	public static int Insert(String table, String columns, String[] newValues)
	{
		String query = "insert into " + table + " (" + columns + ") values ";
		
		for(int x = 0; x < newValues.length - 1; x++)
		{
			query += "( " + newValues[x] + " ),";			
		}
		query += "( " + newValues[newValues.length - 1] + " );";	
		
		return Execute(query);		
	}
	
	public static int Update(String table, String column, String newvalue, String condition)
	{
		return Update(table, new String[]{column}, new String[]{newvalue}, condition);		
	}
	
	public static int Update(String table, String[] columns, String[] newvalues, String condition)
	{
		String query = "update " + table + " set ";
		
		if(columns.length != newvalues.length)
			return -1;
		
		
		query += columns[0] + " = " + newvalues[0];
		for(int x = 1; x < columns.length; x++)
		{
			query += " , "  + columns[x] + " = " + newvalues[x];			
		}		
		query += " where " + condition;
		
		return Execute(query);		
	}
	
	public static int Delete(String table, String condition)
	{
		return Execute("delete from " + table + " where " + condition);		
	}
	
 }
